package com.ruoyi.Material;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@RestController
@RequestMapping("/excel")
public class ExcelUploadController {

    @PostMapping("/uploadExcel")
    public List<Map<String, String>> uploadExcel(@RequestParam("file") MultipartFile file) throws IOException {
        List<Map<String, String>> dataList = new ArrayList<>();
        if (!file.isEmpty()) {
            try (InputStream inputStream = file.getInputStream()) {
                Workbook workbook;
                // 根据文件扩展名判断文件格式并创建相应的 Workbook
                if (file.getOriginalFilename().endsWith(".xls")) {
                    workbook = new HSSFWorkbook(inputStream);
                } else {
                    // 修正这里，.xlsx 格式应该使用 XSSFWorkbook
                    workbook = new XSSFWorkbook(inputStream);
                }
                Sheet sheet = workbook.getSheetAt(0);

                // 获取第 1 行(index=0)中的产品名称和时间
                Row firstRow = sheet.getRow(0);
                String establishment = getCellValue(firstRow.getCell(8));
                // 修正获取时间的列号，从第 9 列获取（索引为 8）

                // 获取第 2 行(index=1)中的客户名称和数量
                Row secondRow = sheet.getRow(1);
                String productName = getCellValue(secondRow.getCell(1));
                String time = getCellValue(secondRow.getCell(8));

                Row thirdRow = sheet.getRow(2);
                String customerName = getCellValue(thirdRow.getCell(1));
                String quantityOverall = getCellValue(secondRow.getCell(8));
                // 从第 3 行(index=2)开始加载数据
                for (int rowNum = 3; rowNum < sheet.getLastRowNum(); rowNum += 6) {
                    Map<String, String> dataMap = new HashMap<>();
                    Map<String,String> data = new HashMap<>();
                    dataMap.put("产品名称", productName);
                    dataMap.put("时间", time);
                    dataMap.put("客户名称", customerName);
                    dataMap.put("数量", quantityOverall);
                    dataMap.put("编制", establishment);
                    data.put("产品名称", productName);
                    data.put("时间", time);
                    data.put("客户名称", customerName);
                    data.put("数量", quantityOverall);
                    data.put("编制", establishment);

                    // 处理左侧数据（图号/名称及其相关属性）
                    Row mainRow = sheet.getRow(rowNum);
                    if (mainRow!= null) {
                        String mainDrawingNumberName = getCellValue(mainRow.getCell(1));
                        if (mainDrawingNumberName!= null &&!mainDrawingNumberName.isEmpty()) {
                            dataMap.put("图号/名称", mainDrawingNumberName);
                        }
                        for (int offset = 1; offset <= 5; offset++) {
                            Row subRow = sheet.getRow(rowNum + offset);
                            if (subRow!= null) {
                                String key = getCellValue(subRow.getCell(0));
                                String value = getCellValue(subRow.getCell(1));
                                if (key!= null &&!key.isEmpty() && value!= null &&!value.isEmpty()) {
                                    dataMap.put(key, value);
                                }
                            }
                        }
                    }

                    // 处理右侧数据（另一个图号/名称及其相关属性）
                    if (mainRow!= null) {
                        String rightDrawingNumberName = getCellValue(mainRow.getCell(5));
                        if (rightDrawingNumberName!= null &&!rightDrawingNumberName.isEmpty()) {
                            data.put("图号/名称", rightDrawingNumberName);
                        }
                        for (int offset = 1; offset <= 5; offset++) {
                            Row subRow = sheet.getRow(rowNum + offset);
                            if (subRow!= null) {
                                String key = getCellValue(subRow.getCell(4));
                                String value = getCellValue(subRow.getCell(5));
                                if (key!= null &&!key.isEmpty() && value!= null &&!value.isEmpty()) {
                                    data.put(key, value);
                                }
                            }
                        }
                    }
                    dataList.add(dataMap);
                    dataList.add(data);
                }
            }
        }
        return dataList;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
}
}
